package com.bw.ads;

import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.TableEnvironment;


public class FlinkTM5_T_2_可用 {
    public static void main(String[] args) throws Exception {

        //1、批环境
        //配置 blink planner 的批式查询
        EnvironmentSettings bbSettings = EnvironmentSettings.newInstance().useBlinkPlanner().inBatchMode().build();
        TableEnvironment bbTableEnv = TableEnvironment.create(bbSettings);

        bbTableEnv.executeSql("create table t_yk8(\n" +
                "            stt string,\n" +
                "            edt string,\n" +
                "            sku_id string,\n" +
                "            sku_name string,\n" +
                "            province_id string,\n" +
                "            province_name string,\n" +
                "            user_id string,\n" +
                "            order_sku_num  BIGINT ,\n" +
                "            order_amount DECIMAL(7,2),\n" +
                "            order_ct BIGINT,\n" +
                "            ts bigint,\n" +
                "            cur_date string\n" +
                "        )with(\n" +
                "           'connector' = 'filesystem',\n" +
                "           'path' = 'datas',\n" +
                "           'format' = 'csv'\n" +
                "        )");

//        bbTableEnv.sqlQuery("select * from t_yk8").execute().print();
        //2）、按城市统计热销top5商品，热销规则为该商品销售量在该城市占比超过12%；（4分）
        /**
         *  // 求省份和sku_id 销量
         *  1、select  province_id,sku_id,sum(order_sku_num) skunums from t_yk8 group by province_id,sku_id
         *
         *  2, 根据省份分区,求各个省份的总销量
         *  select *,sum(skunums) over(partition by province_id) pnums from (select  province_id,sku_id,sum(order_sku_num) skunums from t_yk8 group province_id,sku_id)
         *
         *  3. 求商品占总销量的12%商品 并排名
         *  select *,over(partition province_id order by skunums desc)from (select *,sum(skunums) over(partition by province_id) pnums from (select  province_id,sku_id,sum(order_sku_num) skunums from t_yk8 group by province_id,sku_id)) where cast(skunums as double) / pnums > 0.12
         *
         *   4、求top5
         *   select * from (select *,row_number() over(partition by province_id order by skunums desc)  rk from (select *,sum(skunums) over(partition by province_id) pnums from (select  province_id,sku_id,sum(order_sku_num) skunums from t_yk8 group  by province_id,sku_id)) where cast(skunums as double) / pnums > 0.12) where rk <= 5
         */
        bbTableEnv.sqlQuery("select * from (select *,row_number() over(partition by province_id order by skunums desc)  rk from (select *,sum(skunums) over(partition by province_id) pnums from (select  province_id,sku_id,sum(order_sku_num) skunums from t_yk8 group  by province_id,sku_id)) where cast(skunums as double) / pnums > 0.12) where rk <= 5").execute().print();

//        bbTableEnv.sqlQuery("select * from (\n" +
//                "\n" +
//                "select *,row_number() over (partition by province_id order by skunums desc) rk from (\n" +
//                "  select *,sum(skunums) over (partition by province_id ) provincenums from (select \n" +
//                "    province_id,\n" +
//                "    sku_id,\n" +
//                "    sum(order_sku_num) skunums\n" +
//                "from t_yk8 group by province_id,sku_id) \n" +
//                ") where cast(skunums as double)/ provincenums >=0.12\n" +
//                "\n" +
//                ") where rk <= 5").execute().print();



        //3）、按地区统计不同品类商品的渗透率，渗透率=复购量/首次购买量；（4分）
        //TODO 没理解公式意思，按照复购率求 复购率 = 购买2次以上的人数 / 购买总人数
        /*
        select province_id,skuInfoid,userId,count(1) n from t_yk8 group by province_id,skuInfoid,userId
        select province_id,skuInfoid,count(1) f1 from t1 where n >=2 group by province_id,skuInfoid
         */

        //求每个省份，每个商品，每个人购买次数

        /**
         * province_id sku_id  user_id  count
         *   10         10       10      1
         *   10         11       10      2
         *   10         10       11      1
         *   10         11       11      1
         *
         */
        Table t1 = bbTableEnv.sqlQuery("select province_id,sku_id,user_id,count(1) n from t_yk8 group by province_id,sku_id,user_id");
        bbTableEnv.createTemporaryView("t1",t1);


        //求购买2次以上的人数
        /*
             10         11       10      2
         */
        Table t2 = bbTableEnv.sqlQuery("select province_id,sku_id,count(1) f1 from t1 where n >=2 group by province_id,sku_id");
        bbTableEnv.createTemporaryView("t2",t2);

        //购买总人数
        /*
            10  10   2
            10  11   2
         */
        Table t3 = bbTableEnv.sqlQuery("select province_id,sku_id,count(1) f2 from t1 group by province_id,sku_id");
        bbTableEnv.createTemporaryView("t3",t3);



        bbTableEnv.sqlQuery("select t2.province_id,t2.sku_id, cast(f1 as double)/f2 from t2 join t3" +
                " on t2.province_id = t3.province_id "+
                " and t2.sku_id = t3.sku_id ").execute().print();




    }
}
